# -*- coding: utf-8 -*-
import sqlite3
conn=sqlite3.connect(r'cd.db3')
conn.text_factory = str
try:
	conn.execute('''CREATE TABLE crm(
		id TEXT PRIMARY KEY,
		company TEXT NOT NULL,
		boxs INTEGER NOT NULL DEFAULT 0,
		cds INTEGER NOT NULL DEFAULT 0,
		address TEXT NOT NULL DEFAULT '',
		location INTEGER NOT NULL DEFAULT 0,
		target TEXT NOT NULL DEFAULT '',
		change TEXT NOT NULL DEFAULT '',
		source TEXT NOT NULL DEFAULT '',
		web TEXT NOT NULL DEFAULT '',
		forget INTEGER NOT NULL DEFAULT 0,
		empe INTEGER NOT NULL DEFAULT 0,
		post_update DATETIME NOT NULL DEFAULT '',
		visit INTEGER NOT NULL DEFAULT 0,
		visit_startdate DATE NOT NULL DEFAULT '',
		visit_enddate DATE NOT NULL DEFAULT '',
		visit_address TEXT NOT NULL DEFAULT '',
		assist INTEGER NOT NULL DEFAULT 0,
		assist_content TEXT NOT NULL DEFAULT '',
		touch INTEGER NOT NULL DEFAULT 0,
		success INTEGER NOT NULL DEFAULT 0,
		post INTEGER NOT NULL DEFAULT 1,
		dispatcher INTEGER NOT NULL DEFAULT 0,
		dispatch_update DATETIME NOT NULL DEFAULT '',
		crm_update DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
		memo TEXT NOT NULL DEFAULT ''
	)''')
	
	conn.execute('''CREATE INDEX [index_boxs] ON [crm] ([boxs] DESC)''')
	conn.execute('''CREATE INDEX [index_update] ON [crm] ([crm_update] DESC)''')
	
	conn.execute('''CREATE TABLE crm_location(
		id INTEGER PRIMARY KEY,
		location TEXT NOT NULL UNIQUE
	)''')
	# conn.execute('''CREATE TABLE crm_target(
		# id INTEGER PRIMARY KEY,
		# company TEXT NOT NULL REFERENCES [crm]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		# target INTEGER NOT NULL REFERENCES [country]([id])
	# )''')
	# conn.execute('''CREATE TABLE crm_change(
		# id INTEGER PRIMARY KEY,
		# company TEXT NOT NULL REFERENCES [crm]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		# change INTEGER NOT NULL REFERENCES [change]([id])
	# )''')
	
	# 结果：文本，不能NULL，唯一
	# 结果：未联系0，失败1，成功2
	# conn.execute('''CREATE TABLE crm_result(
		# id INTEGER PRIMARY KEY,
		# result TEXT NOT NULL UNIQUE
	# )''')
	# conn.execute('''CREATE TABLE crm_post(
		# id INTEGER PRIMARY KEY,
		# post TEXT NOT NULL UNIQUE
	# )''')
	
	# 权限：查询1->查看2->新增3->编辑4->删除5->管理15
	# 管理能做派单操作
	conn.execute('''CREATE TABLE crm_flag(
		id INTEGER PRIMARY KEY,
		flag TEXT NOT NULL UNIQUE
	)''')
	
	# 部门：文本，不能NULL，唯一
	conn.execute('''CREATE TABLE crm_dept(
		id INTEGER PRIMARY KEY,
		dept TEXT NOT NULL UNIQUE
	)''')
	# 组：文本，不能NULL，唯一
	# conn.execute('''CREATE TABLE crm_grop(
		# id INTEGER PRIMARY KEY,
		# grop TEXT NOT NULL UNIQUE
	# )''')
	
	# conn.execute('''CREATE TABLE crm_gropdept(
		# grop INTEGER NOT NULL REFERENCES [crm_grop]([id]),
		# dept INTEGER NOT NULL REFERENCES [crm_dept]([id])
	# )''')
	
	# 员工：文本，不能NULL，外键为部门id，级联删除和编辑
	conn.execute('''CREATE TABLE crm_empe(
		id INTEGER PRIMARY KEY,
		dept INTEGER NOT NULL REFERENCES [crm_dept]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		empe TEXT NOT NULL,
		captain INTEGER NOT NULL DEFAULT 0,
		username TEXT NOT NULL UNIQUE,
		password TEXT NOT NULL DEFAULT '',
		active INTEGER NOT NULL DEFAULT 1,
		memo TEXT NOT NULL DEFAULT ''
	)''')
		
	
	# 岗位流转
	# 初始1->派单2<->接单3->完成4
	#   				^							|
	#   				|							|
	#   				---------------
	conn.execute('''CREATE TABLE crm_post(
		id INTEGER PRIMARY KEY,
		post TEXT NOT NULL UNIQUE
	)''')
	
	# 员工权限分配
	conn.execute('''CREATE TABLE crm_permit(
		empe INTEGER NOT NULL REFERENCES [crm_empe]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		post INTEGER NOT NULL REFERENCES [crm_post]([id]),
		flag INTEGER NOT NULL REFERENCES [crm_flag]([id]),
		PRIMARY KEY (empe,post,flag)
	)''')
	
	conn.execute('''CREATE TABLE crm_contact(
		id INTEGER PRIMARY KEY,
		company TEXT NOT NULL REFERENCES [crm]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
		name TEXT NOT NULL DEFAULT '',
		sex INTEGER NOT NULL DEFAULT 0,
		age INTEGER NOT NULL DEFAULT 0,
		telephone TEXT NOT NULL DEFAULT '',
		qq TEXT NOT NULL DEFAULT '',
		msn TEXT NOT NULL DEFAULT '',
		email TEXT NOT NULL DEFAULT '',
		memo TEXT NOT NULL DEFAULT '',
		contact_update DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
		empe INTEGER NOT NULL REFERENCES [crm_empe]([id])
	)''')
	
	# 流转记录
	conn.execute('''CREATE TABLE crm_dispatch(
		id INTEGER PRIMARY KEY,
		company TEXT NOT NULL REFERENCES [crm]([id]),
		dispatcher INTEGER NOT NULL REFERENCES [crm_empe]([id]),
		accepter INTEGER NOT NULL REFERENCES [crm_empe]([id]),
		dispatch_update DATETIME NOT NULL DEFAULT (datetime('now','localtime'))
	)''')
	
	# 处理记录
	conn.execute('''CREATE TABLE crm_process(
		id INTEGER PRIMARY KEY,
		company TEXT NOT NULL REFERENCES [crm]([id]),
		process_content TEXT NOT NULL DEFAULT '',
		empe INTEGER NOT NULL REFERENCES [crm_empe]([id]),
		process_update DATETIME NOT NULL DEFAULT (datetime('now','localtime'))
	)''')
	conn.execute('''CREATE TABLE crm_ul(
		id INTEGER PRIMARY KEY,
		ul_content TEXT NOT NULL DEFAULT '',
		ul_post INTEGER NOT NULL,
		ul_update DATETIME NOT NULL DEFAULT (datetime('now','localtime'))
	)''')
	conn.commit()
except:
	pass
	
# 初始化企业数据
crm=conn.execute("select id,company,boxs,cds from (select cd_companyid,sum(cd_box) as boxs,count(cd_companyid) as cds from cd group by cd_companyid) left join company on id=cd_companyid").fetchall()
for r in crm:
	# print r[0],r[1],r[2],r[3]
	id=str(r[0])
	company=str(r[1])
	if(r[2]==None):
		boxs="0"
		print id,boxs
	else:
		boxs=str(r[2])
	cds=str(r[3])
	# print 
	rs=conn.execute("select countryname,boxs from rpt_companycountry where companyid='"+id+"' order by boxs desc limit 10").fetchall()
	target=[]
	for x in rs:
		target.append(x[0]+"("+str(x[1])+")")
	target=",".join(target)
	rs=conn.execute("select sourcename,boxs from rpt_companysource where companyid='"+id+"' order by boxs desc limit 10").fetchall()
	source=[]
	for x in rs:
		source.append(x[0]+"("+str(x[1])+")")
	source=",".join(source)
	rs=conn.execute("select changename,boxs from rpt_companychange where companyid='"+id+"' order by boxs desc limit 10").fetchall()
	change=[]
	for x in rs:
		change.append(x[0]+"("+str(x[1])+")")
	change=",".join(change)
	# source=",".join(conn.execute("select sourcename||'('||ifnull(boxs,0)||')' from rpt_companysource where companyid='"+id+"' order by boxs desc limit 10").fetchall())
	# change=",".join(conn.execute("select changename||'('||ifnull(boxs,0)||')' from rpt_companychange where companyid='"+id+"' order by boxs desc limit 10").fetchall())
	
	conn.execute("insert into crm(id,company,boxs,cds,target,source,change) values('"+id+"','"+company+"',"+boxs+","+cds+",'"+target+"','"+source+"','"+change+"')")
conn.commit()

# 初始化权限数据
conn.execute("insert into crm_flag(id,flag) values(1,'查询')")
conn.execute("insert into crm_flag(id,flag) values(2,'查询、查看')")
conn.execute("insert into crm_flag(id,flag) values(3,'查询、查看、新增')")
conn.execute("insert into crm_flag(id,flag) values(4,'查询、查看、新增、编辑')")
conn.execute("insert into crm_flag(id,flag) values(5,'查询、查看、新增、编辑、删除')")
conn.execute("insert into crm_flag(id,flag) values(15,'管理')")

# 初始化岗位数据
conn.execute("insert into crm_post(id,post) values(0,'用户权限')")
conn.execute("insert into crm_post(id,post) values(1,'初始')")
conn.execute("insert into crm_post(id,post) values(2,'派单')")
conn.execute("insert into crm_post(id,post) values(3,'接单')")
conn.execute("insert into crm_post(id,post) values(4,'完成')")

# 初始化部门数据
conn.execute("insert into crm_dept(id,dept) values(0,'管理层')")
conn.execute("insert into crm_dept(id,dept) values(1,'第1组')")
conn.execute("insert into crm_dept(id,dept) values(2,'第2组')")
conn.execute("insert into crm_dept(id,dept) values(3,'第3组')")
conn.execute("insert into crm_dept(id,dept) values(4,'第4组')")
conn.execute("insert into crm_dept(id,dept) values(5,'第5组')")
conn.execute("insert into crm_dept(id,dept) values(6,'第6组')")
conn.execute("insert into crm_dept(id,dept) values(7,'第7组')")
conn.execute("insert into crm_dept(id,dept) values(8,'第8组')")
conn.execute("insert into crm_dept(id,dept) values(9,'第9组')")

# 初始化员工数据
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,0,'管理员',1,'admin','admin@test',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,0,'余军',1,'yujun','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,0,'陈凯',0,'chenkai','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,1,'施科鲁',1,'shikl','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,1,'王佳锴',0,'wangjk','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,1,'童超',0,'tongchao','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,2,'涂琳',1,'tuling','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,2,'黄颖',0,'huangyin','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,2,'许振威',0,'xuzw','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,3,'高佳',1,'gaojia','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,3,'仇姚娜',0,'qiuyn','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,3,'陆耀辉',0,'luyh','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,4,'徐晨晖',1,'xuch','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,4,'黄珮榜',0,'huangpb','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,4,'陆苏君',0,'lusj','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,5,'庄健',1,'zhuangjian','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,5,'计凌峰',0,'jilf','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,5,'徐伟彬',0,'xuwb','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,6,'许玉龙',1,'xuyl','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,6,'葛开虹',0,'gekh','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,6,'方露',0,'fanglu','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,7,'郑琪',1,'zhengqi','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,7,'曾燕丰',0,'zengyf','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,8,'陈蓉',1,'chenrong','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,8,'徐盈盈',0,'xuyy','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,8,'周龑',0,'zhougong','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,9,'廖长涛',1,'liaoct','',1)")
conn.execute("insert into crm_empe(id,dept,empe,captain,username,password,active) values(Null,9,'许炎',0,'xuyan','',1)")

conn.execute("insert into crm_location(location) values('宁波')")

# 初始化员工权限分配
conn.execute("insert into crm_permit(empe,post,flag) values(1,0,15)")
conn.execute("insert into crm_permit(empe,post,flag) values(1,1,15)")
conn.execute("insert into crm_permit(empe,post,flag) values(1,2,15)")
conn.execute("insert into crm_permit(empe,post,flag) values(1,3,15)")
conn.execute("insert into crm_permit(empe,post,flag) values(1,4,15)")

conn.commit()
conn.close()
